<?php

# This class handles all data persistence related functions, a.k.a. writing to database and querying database.
class PersistenceManager {
  
  #Store the database connection here.
  var $dbLink;
  
  #Constructor
  #$dbServer Database server address
  #$dbUser Database username to use
  #$dbPassword Database password to use
  #$dbName Database name to use
  function __construct($dbServer, $dbUser, $dbPassword, $dbName) {
    $this->dbLink = mysql_connect($dbServer, $dbUser, $dbPassword);
    if(!$this->dbLink) exit('Database connection failed: '. mysql_error());
    if(!mysql_select_db($dbName,  $this->dbLink)) exit('Database selection failed: '. mysql_error());
  }
  
  function __destruct() {
    mysql_close($this->dbLink);
  }
  
  #Set the geolocation of a locationalias. The given geolocation MUST exist beforehand. This is to catch typing errors when entering geolocations by hand.
  #$geolocation The name of the geolocation (NOT id)
  #$locationAlias The name of the locationalias (NOT id)
  #Return TRUE if successfull, FALSE if failed
  function setAliasGeolocation($locationAlias, $geolocation) {
    #Get Id of the given locationalias
    $locationResult = mysql_query("SELECT idlocationalias FROM locationalias WHERE locationalias = '$locationAlias'", $this->dbLink);
    if(mysql_num_rows($locationResult) == 0) {
      echo("Could not find locationAlias $locationAlias\n");
      return(FALSE);
    }
    $locationResultRow = mysql_fetch_assoc($locationResult);
    $idlocationalias = $locationResultRow['idlocationalias'];
    
    #Get Id of the given geolocation
    $geoResult = mysql_query("SELECT idgeolocation FROM geolocation WHERE geolocation = '$geolocation'", $this->dbLink);    
    if(mysql_num_rows($geoResult) == 0) {
      echo("Could not find geolocation $locationAlias\n");
      return(FALSE);
    }
    $geoResultRow = mysql_fetch_assoc($geoResult);
    $idgeolocation = $geoResultRow['idgeolocation'];
  
    #Update locationalias
    $updateResult = mysql_query("
      UPDATE locationalias 
      SET geolocation_idgeolocation = $idgeolocation 
      WHERE idlocationalias = $idlocationalias", $this->dbLink);
    return($updateResult);
  }
  
  
  #Insert a new geolocation. Ignore duplicates.
  #$geolocation The new geolocation to insert
  #Return the id of the new geolocation, id of the existing geolocation or FALSE if failure
  function insertGeolocation($geolocation) {
    $selectResult = mysql_query("SELECT idgeolocation FROM geolocation WHERE geolocation = '$geolocation'", $this->dbLink);
    if(!$selectResult) return(FALSE);
    if(mysql_num_rows($selectResult) == 0) {
      if(!$this->insert('geolocation', array('geolocation' => $geolocation))) return FALSE;
      return(mysql_insert_id($this->dbLink));
    } else {
      $resultRow = mysql_fetch_assoc($selectResult);
      return($resultRow["idgeolocation"]);
    }
  }
  
  #Insert a new link between two actors on a set date if it doesn't already exist. This means that you don't need to check if a link already exists when crawling since duplicates are ignored.
  #The order of the friendIDs doesn't matter.
  #$friendId0 Id of the first actor
  #$friendId1 Id of the second actor
  #$linkDate Date the link was crawled
  #Return the id of the created link or the id of the previously existing link or FALSE if failure
  function insertLink($friendId0, $friendId1, $linkDate) {
    $selectResult = mysql_query(
        "SELECT idlink FROM link WHERE linkdate = '$linkDate' AND (
          (actor_friendnumber0 = $friendId0 AND actor_friendnumber1 = $friendId1) OR 
          (actor_friendnumber1 = $friendId0 AND actor_friendnumber0 = $friendId1)
        )"
        , $this->dbLink);
    
    if(!$selectResult) return(FALSE);
    if(mysql_num_rows($selectResult) == 0) {
      if(!$this->insert('link', array('linkdate' => $linkDate, 'actor_friendnumber0' => $friendId0, 'actor_friendnumber1' => $friendId1)))
        return FALSE;
      return(mysql_insert_id($this->dbLink));
    } else { 
      $resultRow = mysql_fetch_assoc($selectResult);
      return($resultRow["idlink"]);
    }
  }
  
  
  #Insert a new artist into the database.
  #$friendNumber Friend number of the artist.
  #$age The age of the artist
  #$gender The gender of the artist. Use 0 for female and 1 for male.
  #$region The region of the artist
  #$locationAlias The location alias of the artist. NOTE: If NULL is given value 'unknown' is used instead.
  #Return True if insertion succeeded, false if it failed
  function insertArtist($friendNumber, $name, $age, $gender, $region, $locationAlias) {
    if(!$this->insertFriend($friendNumber, $name, $age, $gender, $region, $locationAlias))
      return(FALSE);
    if($this->insert('artist', array('actor_friendnumber' => $friendNumber, 'haslabel' => 0)))
      return(FALSE);
  }
  
  #Insert a new friend into the database.
  #$friendNumber Friend number of the friend.
  #$age The age of the friend
  #$gender The gender of the friend
  #$region The region of the frined
  #$locationAlias The location alias of the friend. NOTE: If NULL is given value 'unknown' is used instead.
  #Return True if insertion succeeded, false if it failed
  function insertFriend($friendNumber, $name, $age, $gender, $region, $locationAlias) {
    if(is_null($locationAlias)) $locationAlias = 'unknown';
    $idLocationAlias = $this->insertLocationAlias($locationAlias);
    if(!$idLocationAlias) {
      echo("Failed to insert location alias\n");
      return (FALSE);
    }
    return($this->insert('actor', array(
        'friendNumber' => $friendNumber, 
        'name' => $name,
        'age' => $age, 
        'gender' => $gender, 
        'region' => $region,
        'locationalias_idlocationalias' => $idLocationAlias)));
  }
  
  #Insert a new location alias to the database if it doesn't exist and return created id. If it already exists, return existing alias id.
  #$locationAliast Location alias string to insert
  #Return The id of the created location alias or the id of the existing matching location alias.
  function insertLocationAlias($locationAlias) {
    $selectResult = mysql_query("SELECT idlocationalias FROM locationalias WHERE locationalias = '$locationAlias'", $this->dbLink);
    if(!$selectResult) return(FALSE);
    if(mysql_num_rows($selectResult) == 0) {
      # Location alias doesn't exists. Create it and return the id of creted row.
      if(!$this->insert('locationalias', array('locationalias' => $locationAlias))) return(FALSE);
      return(mysql_insert_id($this->dbLink));
    } else {
      # Location alias exists, fetch the id of the row and return it.
      $resultRow = mysql_fetch_assoc($selectResult);
      return($resultRow["idlocationalias"]);
    }
  }
  
  #Insert given velues to given rows in given table. NOTE: Use outside this class only for testing!
  #$table Name of the table to insert the values to.
  #$values Array of column name => value pairs to be inserted. Null valued columns are skipped.
  #Returns TRUE if insertion successed, FALSE if insertion failed
  function insert($table, $values) {
    $colString = '';
    $valueString = '';
    foreach($values as $col => $value) {
      if(!is_null($value)) { #Skip columns with null value
        if($colString != '') $colString .= ','; #Insert "," between colums
        if($valueString != '') $valueString .= ','; #Insert "," between values
        $colString .= "$col";  
        if(is_int($value) Or is_float($value)) $valueString .= $value;
        elseif(is_string($value)) $valueString .= "'" . mysql_real_escape_string($value, $this->dbLink) . "'"; #Properly escape strings      
        else {
          echo('Don\'t know how to persist datatype ' . gettype($value));
          return(FALSE);
        }
      }
    }
    $query = sprintf("INSERT INTO %s(%s) VALUES(%s)", $table, $colString, $valueString);
    echo("$query\n");
    return(mysql_query($query, $this->dbLink));
  }
  
}

?>